IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FindWorkflowEmailsSent]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[FindWorkflowEmailsSent]
GO

SET QUOTED_IDENTIFIER OFF
GO

/************************************************************************************************        
* Name:  [FindWorkflowEmailsSent]                                                                  
* Purpose: This stored procedure gets the email generated(scrapped form chunk of data) having the       
*     passed in executionId       
*        
*          
* PARAMETERS          
* Name   Description        
* ------------------------- -----------------------------        
* @ExecutionId    Workflow execution Id        
*        
************************************************************************************************        
*Call: [FindWorkflowEmailsSent] 6        
************************************************************************************************/       
CREATE PROCEDURE [dbo].[FindWorkflowEmailsSent]    
(        
 @emailAddress varchar(300)         
)        
As Begin      
SET NOCOUNT ON      
      
 Declare @counter int      
 Declare @totalrecords int      
       
 Declare @Email as Table(       
       Id integer Identity(1,1),      
       ExecutionDetailsId integer, 
	   SWExecutionId int,
       ProtocolProcessId int,
	   LastUpdatedDateTime datetime,
	   EmailDescription nvarchar(max)      
       )      
    
Insert into @Email Select swe.Id as ExecutionDetailsId,    
	  swe.ExecutionId,
	  map.ProtocolProcessId,
	  swe.LastUpdatedDateTime,
      swl.CharData as EmailDescription       
       from swactioneventlong swl     
    inner join swActionEventLog swal    
    On Swal.LogMessage = swl.Id     
       inner join swexecutionDetails swe      
       on swe.[Id] = swal.[executionDetailsId]  
	inner join ProtocolProcess_Workflow_Map map
		on map.executionId = swe.ExecutionId    
       where 
		--swe.ExecutionId in (Select distinct(ExecutionId) from SWExecutionDetails where LastUpdatedDateTime > GetDate()-1 )
--       and swe.ActionType in ('Email','Approval')  
--	   and 
		swl.CharData like '%' + @emailAddress + '%'
 
	if exists (Select 1 from @Email)
	BEGIN   
		 DECLARE @XmlDocumentHandle int      
		 DECLARE @XmlDocument nvarchar(max)      
		 declare @EmailDesc table ([ExecutionDetailsID] int, SWExecutionId int, ProtocolProcessId int, LastUpdatedDateTime datetime, EmailDescription nvarchar(max))                
		 Set @counter = 1      
		 Set @totalRecords = (Select count(*) from @Email)    
		PRINT @totalRecords  
		 While @Counter < = @totalRecords      
		  BEGIN      
		   Declare @ExecutionDetailsId integer      
		   Declare @SWExecutionId int
		   Declare @ProtocolProcessId int
		   Declare @LastUpdatedDateTime datetime
		   Select @ExecutionDetailsId =ExecutionDetailsId,
				@SWExecutionId = SWExecutionId,
				@ProtocolProcessId = ProtocolProcessId ,
				@LastUpdatedDateTime = LastUpdatedDateTime,
				@XmlDocument = EmailDescription from @Email where id = @counter      
		--      
		   -- Create an internal representation of the XML document.      
		   EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument      
		   -- Execute a SELECT statement using OPENXML rowset provider.      
		   Insert into @EmailDesc (ExecutionDetailsId, SWExecutionId, ProtocolProcessId, LastUpdatedDateTime, EmailDescription)         
		   SELECT @ExecutionDetailsId,@SWExecutionId, @ProtocolProcessId, @LastUpdatedDateTime, rtrim(Ltrim([description])) as EmailDescription           
		   FROM OPENXML (@XmlDocumentHandle, '//message[@type="ActionEvent" or @type="ResourceActivity"]',2)      
		   WITH (description  nvarchar(max))      
		         
		   Set @counter = @Counter + 1      
		      
		  END      
		  EXEC sp_xml_removedocument @XmlDocumentHandle	   
	END   
Select * from @EmailDesc where EmailDescription like '%' + @emailAddress + '%' Order by LastUpdatedDateTime
End      
